package com.internetCafes.spms.common.module.mybatis.plugin;

import com.google.common.collect.Lists;
import com.internetCafes.spms.common.utils.ShiroUtils;
import com.sms.common.util.hutool.core.util.ObjectUtil;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.plugin.Invocation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * sql解析及处理
 * Created by Meara on 2016/6/8.
 */

public class SqlParserUtils {
    private static final Logger logger = LoggerFactory.getLogger(SqlParserUtils.class);

    private static String tenantIdColumn = "tenant_id";

    public static String parseSQL(String sql, String tableAlias, Long tenantId) {
        //logger.debug("old sql:{}",sql);
        Statement stmt;
        try {
            stmt = CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            logger.debug("解析", e);
            logger.error("解析sql[{}]失败\n原因:{}", sql, e.getMessage());
            //如果解析失败不进行任何处理防止业务中断
            return sql;
        }
        if (stmt instanceof Insert) {
            processInsert((Insert) stmt, tenantId);
        }
        if (stmt instanceof Select) {
            Select select = (Select) stmt;
            processSelectBody(select.getSelectBody(), tableAlias, tenantId);
        }
        if (stmt instanceof Update) {
            processUpdate((Update) stmt, tenantId);
        }
        //logger.debug("new sql:{}",stmt);
        return stmt.toString().replace("OFFSET", ",");
    }


    public static void processInsert(Insert insert, Long tenantId) {
        insert.getColumns().add(new Column(tenantIdColumn));
        if (ObjectUtil.isNull(ShiroUtils.getUserEntity())) {
            return;
        }
        if (insert.getSelect() != null) {
            processPlainSelect((PlainSelect) insert.getSelect().getSelectBody(), null, true, tenantId);
        } else if (insert.getItemsList() != null) {
            ((ExpressionList) insert.getItemsList()).getExpressions().add(new StringValue("," + tenantId + ","));
        } else {
            throw new RuntimeException("无法处理的 sql");
        }
    }


    /**
     * update语句处理
     * TODO 因为线上系统不允许更改数据租户,所以并未实现它
     *
     * @param update
     */
    public static void processUpdate(Update update, Long tenantId) {
        Expression where = update.getWhere();
        EqualsTo equalsTo = new EqualsTo();
        if (where instanceof BinaryExpression) {
            equalsTo.setLeftExpression(new Column(tenantIdColumn));
            equalsTo.setRightExpression(new StringValue("," + tenantId + ","));
            AndExpression andExpression = new AndExpression(equalsTo, where);
            update.setWhere(andExpression);
        } else {
            equalsTo.setLeftExpression(new Column(tenantIdColumn));
            equalsTo.setRightExpression(new StringValue("," + tenantId + ","));
            update.setWhere(equalsTo);
        }
    }


    /**
     * 处理联接语句
     *
     * @param join
     */
    public static void processJoin(Join join, String tableAlias, Long tenantId) {
        if (join.getRightItem() instanceof Table) {
            Table fromTable = (Table) join.getRightItem();
            if (StringUtils.isNotBlank(tableAlias)) {
                //fromTable.setAlias(new Alias(tableAlias));
            }
            join.setOnExpression(builderExpression(join.getOnExpression(), fromTable, new Long[]{tenantId}));
        }
    }


    /**
     * 处理条件
     *
     * @param expression
     * @param table
     * @return
     */
    public static Expression builderExpression(Expression expression, Table table, Long[] tenantIds) {
        Expression tenantExpression;
        //当传入table时,字段前加上别名或者table名
        //别名优先使用
        StringBuilder tenantIdColumnName = new StringBuilder();
        if (table != null && table.getAlias() != null) {
            tenantIdColumnName.append(table.getAlias().getName());
            tenantIdColumnName.append(".");
        }
        tenantIdColumnName.append(tenantIdColumn);
        //生成字段名
        Column tenantColumn = new Column(tenantIdColumnName.toString());

        if (tenantIds.length == 1) {
            EqualsTo equalsTo = new EqualsTo();
            tenantExpression = equalsTo;
            equalsTo.setLeftExpression(tenantColumn);
            equalsTo.setRightExpression(new LongValue(tenantIds[0]));
        } else {
            //多租户身份
            InExpression inExpression = new InExpression();
            tenantExpression = inExpression;
            inExpression.setLeftExpression(tenantColumn);
            List<Expression> valueList = new ArrayList<>();
            for (Long tenantId : tenantIds) {
                valueList.add(new LongValue(tenantId));
            }
            inExpression.setRightItemsList(new ExpressionList(valueList));
        }

        //加入判断防止条件为空时生成 "and null" 导致查询结果为空
        if (expression == null) {
            return tenantExpression;
        } else {
            if (expression instanceof BinaryExpression) {
                BinaryExpression binaryExpression = (BinaryExpression) expression;
                if (binaryExpression.getLeftExpression() instanceof FromItem) {
                    processFromItem((FromItem) binaryExpression.getLeftExpression(), table.getAlias() != null ? table.getAlias().getName() : "", tenantIds[0]);
                }
                if (binaryExpression.getRightExpression() instanceof FromItem) {
                    processFromItem((FromItem) binaryExpression.getRightExpression(), table.getAlias() != null ? table.getAlias().getName() : "", tenantIds[0]);
                }
            }
            return new AndExpression(tenantExpression, expression);
        }

    }


    /**
     * 处理SelectBody
     */
    public static void processSelectBody(SelectBody selectBody, String tableAlias, Long tenantId) {
        if (selectBody instanceof PlainSelect) {
            processPlainSelect((PlainSelect) selectBody, tableAlias, tenantId);
        } else if (selectBody instanceof WithItem) {
            WithItem withItem = (WithItem) selectBody;
            if (withItem.getSelectBody() != null) {
                processSelectBody(withItem.getSelectBody(), tableAlias, tenantId);
            }
        } else {
            SetOperationList operationList = (SetOperationList) selectBody;
            if (operationList.getSelects() != null && operationList.getSelects().size() > 0) {
                List<SelectBody> plainSelects = operationList.getSelects();
                for (SelectBody plainSelect : plainSelects) {
                    processSelectBody(plainSelect, tableAlias, tenantId);
                }
            }
        }
    }


    /**
     * 处理PlainSelect
     */

    public static void processPlainSelect(PlainSelect plainSelect, String tableAlias, Long tenantId) {
        processPlainSelect(plainSelect, tableAlias, false, tenantId);
    }

    /**
     * 处理PlainSelect
     *
     * @param plainSelect
     * @param addColumn   是否添加租户列,insert into select语句中需要
     */

    public static void processPlainSelect(PlainSelect plainSelect, String tableAlias, boolean addColumn, Long tenantId) {
        FromItem fromItem = plainSelect.getFromItem();
        if (ObjectUtil.isNotNull(fromItem.getAlias())) {
            if (fromItem.getAlias().getName().equals("c")
                    || fromItem.getAlias().getName().equals("tui")
                    || fromItem.getAlias().getName().equals("turi")) {
                return;
            }
        }
        if (fromItem instanceof Table) {
            Table fromTable = (Table) fromItem;
            plainSelect.setWhere(builderExpression(plainSelect.getWhere(), fromTable, new Long[]{tenantId}));
            if (addColumn) {
                plainSelect.getSelectItems().add(new SelectExpressionItem(new Column(tenantId + "")));
            }
        } else {
            processFromItem(fromItem, tableAlias, tenantId);
        }
        List<Join> joins = plainSelect.getJoins();
        if (joins != null && joins.size() > 0) {
            for (Join join : joins) {
                /*
                 * 该处由于表格中t_user_info
                 *  t_certificate_info 中 无tenant_id字段
                 * 所以以c和tui别名的表LEFT不进行租户字段添加
                 * by Zheng Jun 2021.11.26
                 */
                if (!join.getRightItem().getAlias().getName().equals("c")
                        && !join.getRightItem().getAlias().getName().equals("tui")
                        && !join.getRightItem().getAlias().getName().equals("turi")) {
                    processJoin(join, tableAlias, tenantId);
                }
                processFromItem(join.getRightItem(), tableAlias, tenantId);
            }
        }
    }


    /**
     * 处理子查询等
     *
     * @param fromItem
     */
    public static void processFromItem(FromItem fromItem, String tableAlias, Long tenantId) {
        if (fromItem instanceof SubJoin) {
            SubJoin subJoin = (SubJoin) fromItem;
            List<Join> joinList = subJoin.getJoinList();
            if (joinList != null && joinList.size() > 0) {
                processJoin(joinList.get(0), tableAlias, tenantId);
            }
            if (subJoin.getLeft() != null) {
                processFromItem(subJoin.getLeft(), tableAlias, tenantId);
            }
        } else if (fromItem instanceof SubSelect) {
            SubSelect subSelect = (SubSelect) fromItem;
            if (subSelect.getSelectBody() != null) {
                processSelectBody(subSelect.getSelectBody(), tableAlias, tenantId);
            }
        } else if (fromItem instanceof ValuesList) {

        } else if (fromItem instanceof LateralSubSelect) {
            LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
            if (lateralSubSelect.getSubSelect() != null) {
                SubSelect subSelect = lateralSubSelect.getSubSelect();
                if (subSelect.getSelectBody() != null) {
                    processSelectBody(subSelect.getSelectBody(), tableAlias, tenantId);
                }
            }
        }
    }


    /**
     * 返回默认的值，list类型的返回空list,数值类型的返回0
     *
     * @param invocation
     * @return
     */
    public static Object getDefaultReturnValue(Invocation invocation) {
        Class returnType = invocation.getMethod().getReturnType();
        if (returnType.equals(List.class)) {
            return Lists.newArrayList();
        } else if (returnType.equals(Integer.TYPE) || returnType.equals(Long.TYPE)
                || returnType.equals(Integer.class) || returnType.equals(Long.class)) {
            return 0;
        }
        return null;
    }


    /**
     * 去除字符中的干扰项，避免字符串中的内容干扰判断。
     *
     * @param sql
     * @return
     */
    public static String removeInterference(String sql) {
        Pattern pattern = Pattern.compile("[\"|'](.*?)[\"|']");
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            String replaceWorld = matcher.group();
            sql = sql.replace(replaceWorld, "''");
        }
        return sql;
    }


    /**
     * 判断是否存在空list
     *
     * @param sql
     * @return
     */
    public static Boolean isHaveEmptyList(String sql) {
        sql = removeInterference(sql);
        List<String> keyWorldList = Lists.newArrayList("in", "values");
        Boolean isHaveEmptyList = Boolean.FALSE;
        for (String keyWorld : keyWorldList) {
            List<Integer> indexList = Lists.newArrayList();
            //获取关键词后的index，关键词前必须为空白字符，但以关键词开头的单词也会被匹配到，例如index
            Pattern pattern = Pattern.compile("\\s(?i)" + keyWorld);
            Matcher matcher = pattern.matcher(sql);
            while (matcher.find()) {
                indexList.add(matcher.end());
            }
            if (CollectionUtils.isNotEmpty(indexList)) {
                isHaveEmptyList = checkHaveEmptyList(sql, indexList);
                if (isHaveEmptyList) {
                    break;
                }
            }
        }
        return isHaveEmptyList;
    }


    /**
     * 判断sql在indexList的每个index后是否存在存在空列表的情况
     *
     * @param sql
     * @param indexList keyWorld在sql中的位置
     * @return
     */
    public static Boolean checkHaveEmptyList(String sql, List<Integer> indexList) {
        Boolean isHaveEmptyList = Boolean.FALSE;
        //获取()内的内容
        Pattern p2 = Pattern.compile("(?<=\\()(.+?)(?=\\))");
        for (Integer index : indexList) {
            String subSql = sql.substring(index);
            //如果关键词之后无任何sql语句，则sql语句结尾为关键词，此时判定为空列表
            if (StringUtils.isEmpty(subSql)) {
                isHaveEmptyList = Boolean.TRUE;
                break;
            }
            //关键词后必须是(或者是空字符或者是换行符等才有继续判断的意义，避免sql中存在以关键词in或values开头的单词的情况干扰判断
            boolean flag = subSql.startsWith("(")
                    || subSql.startsWith(" ")
                    || subSql.startsWith("\n")
                    || subSql.startsWith("\r");
            if (!flag) {
                continue;
            }
            subSql = subSql.trim();
            //如果关键词后的sql语句trim后不以(开头，也判定为空列表
            if (!subSql.startsWith("(")) {
                isHaveEmptyList = Boolean.TRUE;
                break;
            }
            Matcher m2 = p2.matcher(subSql);
            //如果括号()内的内容trim后为空，则判定为空列表
            if (m2.find()) {
                if (StringUtils.isEmpty(m2.group().trim())) {
                    isHaveEmptyList = Boolean.TRUE;
                    break;
                }
            }
        }
        return isHaveEmptyList;
    }
}
